package com.fs.base.repository;

import java.util.Collection;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import com.fs.base.pojo.Attachment;

/**
 * @author TiddlerCJ
 */
@CacheConfig(cacheNames = "attachments")
public interface AttachmentRepository extends JpaRepository<Attachment,String>, JpaSpecificationExecutor<Attachment>{
	/**
	 * 通过文件名删除
	 * @param title
	 */
	@Transactional
	void deleteByTitle(String title);
	
	Attachment findByUrl(String url);
	
	/**
	 * 根据id 或是title 或是 url查询对应的附件记录
	 * @param attr
	 * @return
	 */
	@Query(" select a from Attachment a where  a.id =?1 or a.title =?1 or a.url = ?1 ")
	Attachment findAttachmentByAttr(String idOrTitleOrUrl);
	
	
	@Cacheable
	@Query( value=" SELECT * from ( SELECT am.* FROM s_article_payment as ap LEFT JOIN s_article a ON a.id = ap.article_id  LEFT JOIN s_attachment am ON am.url = a.attach_url GROUP BY ap.article_id ORDER BY COUNT(*)    DESC LIMIT ?1 )  as A  WHERE A.id is not null " ,nativeQuery=true)
	List<Attachment> findPaymentsTop(Integer limit);
	
	List<Attachment> findByIdIn(Collection<String> ids);
}
